package com.dbex.core;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.util.List;

import com.dbex.util.DBUtil;
import com.dbex.util.StaticConstant;

/**
 * 输出 SQL 语句:将 tables按照 desDialect的格式导出到 output
 * @author Administrator
 *
 */
public class OutputSQL {
	
	private Table[] tables;
	
	private Dialect desDialect;

	private Dialect srcDialect;
	
	private String desSchema;
	
	private BufferedWriter output = null;
	
	public OutputSQL(Table[] tables, Dialect srcDialect, 
			Dialect desDialect, String desSchema) {
		super();
		this.tables = tables;
		this.desDialect = desDialect;
		this.srcDialect = srcDialect;
		this.desSchema = desSchema;
		initOutput();
	}
	
	private void initOutput() {
		File file = new File(StaticConstant.DEFAULT_EXPORT_FILE);
		if(file.exists()) file.delete();
		try {
			FileOutputStream fos = new FileOutputStream(file);
			output = new BufferedWriter(new OutputStreamWriter(fos,"utf-8"));
		} catch (Exception e) {
			throw new RuntimeException("初始化输出文件失败");
		}
	}



	public void writeSQL(){
		try {
			for (int i = 0; i < tables.length; i++) {
				output.write(geneCreateSql(tables[i]));
				writeInsertSql(tables[i]);
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException("写入SQL语句出错");
		} finally {
			if(output!=null)
				try {output.close();} catch (IOException e) {}
		}
	}
	@SuppressWarnings("unchecked")
	private void writeInsertSql(Table table) throws Exception {
		List<Object[]> list = DBUtil.queryList(geneSelectAllSql(table));
		
		String s = "insert into "+desSchema+"."+table.getTableName()
				+"("+table.getColumnsName(false, null)+") values(";
		
		for (Object[] arr : list) {
			StringBuffer sb = new StringBuffer(s);
			for (int i = 0; i < table.getColumns().length; i++) {
				String columnType = table.getColumns()[i].getColumnType();
				if(arr[i]==null){
					sb.append("null,");
				}else{
					if("string".equals(columnType)){
						sb.append('\'').append(arr[i]).append("\',");
					}else if ("number".equals(columnType)) {
						sb.append(arr[i]).append(',');
					}else if ("date".equals(columnType)) {
						sb.append(desDialect.toDate(arr[i].toString())).append(',');
					}else{
						throw new RuntimeException("不识别的类型");
					}
				}
				
			}
			
			sb.deleteCharAt(sb.length()-1).append(");\n");
			output.write(sb.toString());
		}
		output.write('\n');
	}

	private String geneCreateSql(Table table) {
		StringBuffer sb = new StringBuffer();
		sb.append("create table ").append(desSchema)
		.append(".").append(table.getTableName()).append("(");
		for(Column c:table.getColumns()){
			sb.append("\n\t").append(c.getColumnName()).append(" ")
			.append(convertColumnType(c)).append(",");
		}
		sb.deleteCharAt(sb.length()-1).append("\n);\n");
		return sb.toString();
	}
	
	private String convertColumnType(Column column){
		String columnType = column.getColumnType();
		if("string".equals(columnType)){
			return desDialect.getStringType(column.isVar(), column.getLength());
		}else if ("number".equals(columnType)) {
			return desDialect.getNumberType(column.getPrecision(), column.getScale());
		}else if ("date".equals(columnType)) {
			return "date";
		}else{
			throw new RuntimeException("无法识别的类型"+columnType);
		}
	}
	
	private String geneSelectAllSql(Table table){
		StringBuffer sb = new StringBuffer();
		sb.append("select ");
		for(Column c:table.getColumns()){
			if("date".equals(c.getColumnType())){
				sb.append(srcDialect.toChar(c.getColumnName()));
			}else{
				sb.append(c.getColumnName());
			}
			sb.append(",");
		}
		sb.deleteCharAt(sb.length()-1).append(" from ")
		.append(table.getSchema()+"."+table.getTableName());
		return sb.toString();
	}
	

}
